﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    using DAL.Entities;

    public class PhieusudungdichvuDAL : BaseDAL, UTL.IBaseDAL 
    {
        public int Count()
        {
            throw new NotImplementedException();
        }

        public DataTable Select()
        {
            string sql = @"SELECT *, c.tendichvu from phieusudungdichvu a join phieukham b on a.sttphieu=b.sttphieu and a.ngaykham=b.ngaykham join dichvu_cls c on a.madichvu=c.madichvu";

            return ExecuteQuery(sql);
        }

        public DataTable Select(object obj)
        {
            var o=(phieusudungdichvu)obj;
            string sql = @"SELECT *, c.tendichvu from phieusudungdichvu a join phieukham b on a.sttphieu=b.sttphieu and a.ngaykham=b.ngaykham join dichvu_cls c on a.madichvu=c.madichvu where a.sttphieu='{0}' and a.ngaykham='{1}'";
            sql = string.Format(sql, o.sttphieu, o.ngaykham);
            return ExecuteQuery(sql);
        }

        public object GetByKey(object key)
        {
            throw new NotImplementedException();
        }

        public bool Delete(string id)
        {
            throw new NotImplementedException();

        }

        public bool Insert(object obj)
        {
            var o = (phieusudungdichvu)obj;
            string sql = @"set dateformat mdy; INSERT INTO phieusudungdichvu(sttphieu,ngaykham,madichvu) VALUES('{0}','{1}','{2}')";
            sql = string.Format(sql,o.sttphieu,o.ngaykham,o.madichvu);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool Update(object obj)
        {
            var o = (phieusudungdichvu)obj;
            string sql = @"set dateformat mdy; UPDATE phieusudungdichvu SET ketluan='{0}' WHERE sttphieu='{1}' and madichvu='{2}' and ngaykham='{3}'";
            sql = string.Format(sql, o.ketluan, o.sttphieu, o.madichvu,o.ngaykham);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public DataTable Search(string name)
        {
            throw new NotImplementedException();
        }

        public DataTable Select_sttphieu()
        {
            string sql = @"SELECT sttphieu as id FROM phieukham";
            return ExecuteQuery(sql);
        }

        public DataTable Select_madichvu()
        {
            string sql = @" SELECT madichvu as id, tendichvu as ten FROM dichvu_cls";
            return ExecuteQuery(sql);
        }
        public DataTable Select_madichvuobj(object obj)
        {
            var o = (phieusudungdichvu)obj;
            string sql = @" SELECT madichvu as id, tendichvu as ten FROM dichvu_cls 
                where MADICHVU not in(select MADICHVU from PHIEUSUDUNGDICHVU where STTPHIEU='{0}' and NGAYKHAM='{1}')";
            sql = string.Format(sql, o.sttphieu, o.ngaykham);
            return ExecuteQuery(sql);
        }
        /// <summary>
        /// lay ...
        /// </summary>
        /// <returns></returns>
        public string Select_max()
        {
            string sql = @"SELECT max(sttphieu) as id FROM phieukham";
            var tb= ExecuteQuery(sql);
            return tb.Rows.Count > 0 ? tb.Rows[0]["id"] + "" : "0";
            //string sql = @"SELECT max(madichvu) as id FROM dichvu_cls";
            //var tb = ExecuteQuery(sql);
            
            //return tb.Rows.Count>0? tb.Rows[0]["id"]+"":"0";
        }

        public DataTable Select_Report(string id, DateTime ngay, string sttphieu)
        {
            string sql = @" select a.STTPHIEU as STTphieu,e.HOTEN_BN as Benhnhan, a.NGAYKHAM as Ngaykham,c.TENDICHVU as Dichvu,d.TENLOAIDICHVU as Loaidichvu,f.DONGIA as Dongia
                            from PHIEUSUDUNGDICHVU a join PHIEUKHAM b on a.STTPHIEU = b.STTPHIEU and a.NGAYKHAM = b.NGAYKHAM
                            join DICHVU_CLS c on a.MADICHVU = c.MADICHVU 
                            join LOAIDICHVU d on c.MALOAIDICHVU= d.MALOAIDICHVU
                            join BENHNHAN e on b.MA_BN = e.MA_BN 
                            join DONGIADICHVU_CLS f on c.MADICHVU = f.MADICHVU
                            where a.MADICHVU='{0}' and a.NGAYKHAM='{1}' and a.STTPHIEU='{2}'";
            sql = string.Format(sql, id,ngay,sttphieu);
            return ExecuteQuery(sql);
        
        }

        public bool deleteobj(object obj)
        {
            var o = (phieusudungdichvu)obj;
            string sql = @"set dateformat mdy; DELETE FROM phieusudungdichvu WHERE sttphieu = '{0}' and ngaykham='{1}' and madichvu ='{2}'";
            sql = string.Format(sql,o.sttphieu,o.ngaykham,o.madichvu);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }
    }
}
